package com.tanhua.admin.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tanhua.model.db.Analysis;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

public interface AnalysisMapper extends BaseMapper<Analysis> {
    //前七天活跃用户数据
    @Select("select SUM(num_active)  from tb_analysis where record_date > (select date_sub(NOW(),INTERVAL '7,0:0:0' DAY_SECOND))")
    Integer querySevenUsers();
    //前三十天天活跃用户数据
    @Select("select SUM(num_active) from tb_analysis where record_date > (select date_sub(NOW(),INTERVAL 1 MONTH))")
    Integer queryMonthUsers();

    //今日新增用户
    @Select("select SUM(num_registered)  from tb_analysis where (record_date = #{today})")
    Integer queryRes(String today);

    //累计用户
    @Select("select SUM(num_registered)  from tb_analysis")
    Integer count();

    //今日登录次数
    @Select("select SUM(num_login)  from tb_analysis where (record_date =#{today})")
    Integer queryLogin(String today);

    //某天活跃用户数据
    @Select("select SUM(num_active)  from tb_analysis where record_date = #{today}")
    Integer queryActive(String today);

    //通过开始和结束日期查询新增用户
    @Select("SELECT SUM(num_registered) FROM tb_analysis where record_date BETWEEN #{sd} AND #{ed}")
    Integer queryResUsers(@Param("sd") String sd, @Param("ed") String ed);
    //通过开始和结束日期查询活跃用户
    @Select("SELECT SUM(num_active) FROM tb_analysis where record_date BETWEEN #{sd} AND #{ed}")
    Integer queryActiveUsers(@Param("sd") String sd, @Param("ed") String ed);

    //通过开始和结束日期查询次日留存率
    @Select("")
    Integer queryUsersRetentionId();

    //根据月份年份查询新增
    @Select("select sum(num_registered) from tb_analysis where MONTH(record_date ) = #{month} AND YEAR(record_date) = #{year}")
    Integer queryResCount(@Param("month") Integer month,@Param("year") Integer year);

    //根据月份年份查询活跃
    @Select("select sum(num_active) from tb_analysis where MONTH(record_date ) = #{month} AND YEAR(record_date) = #{year}")
    Integer queryActiveCount(@Param("month") Integer month,@Param("year") Integer year);

    //根据时间查询新增用户
    //@Select("SELECT num_registered FROM `tb_analysis` where record_date = #{format}")
    @Select("SELECT num_registered  FROM `tb_analysis` where record_date = #{format}")
    Integer findRes(@Param("format") String format);


    //根据时间查询活跃用户数
    @Select("SELECT num_active FROM `tb_analysis` where record_date = #{format}")
    Integer findActive(@Param("format") String format);

}